package com.fitc.json;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

import com.fitc.imageprocessing.ColorData;

public class DatabaseHelper {

	// connection/ database attributes
	public static final String driver = "org.gjt.mm.mysql.Driver";
	public static final String url = "jdbc:mysql://localhost/flickr_images";
	public static final String username = "root";
	public static final String password = "password";

	private static final int MIN_TAGS_PER_PHOTO = 2;

	public Connection connection = null;

	public DatabaseHelper() {

		try {
			connection = getConnection(url, username, password);
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	public void deleteAll() {
		Statement stmt;
		try {
			stmt = connection.createStatement();
			String query = "DELETE FROM images";
			int deletedRows = stmt.executeUpdate(query);

			stmt = connection.createStatement();
			query = "DELETE FROM tags";
			deletedRows = stmt.executeUpdate(query);

			stmt = connection.createStatement();
			query = "DELETE FROM image_tag_link";

			deletedRows = stmt.executeUpdate(query);

			if (deletedRows > 0) {
				System.out
						.println("Deleted All Rows In The Table Successfully...");
			} else {
				System.out.println("Table already empty.");
			}

		} catch (SQLException s) {
			System.out.println("Deleted All Rows In  Table Error. ");
			s.printStackTrace();
		}
	}

	public boolean insertPhotoSet(Set<Photo> photos) {
		PreparedStatement pst1, pst2, pst3 = null;
		Iterator<Photo> iterator = photos.iterator();

		try {
			connection.setAutoCommit(false);

			pst1 = connection
					.prepareStatement("INSERT INTO images(idimage,title,url,alias) VALUES (?,?,?,?)");
			pst2 = connection
					.prepareStatement("INSERT INTO tags(tag,count) VALUES (?,1) ON DUPLICATE KEY UPDATE count = count + 1");
			pst3 = connection
					.prepareStatement("INSERT INTO image_tag_link(fk_idimage,fk_tag) VALUES (? , ?)");

			while (iterator.hasNext()) {
				Photo p = (Photo) iterator.next();
				String[] tags = p.tokenisedTags(true);

				/*
				 * Only add photo to db if more than two tags AND have a title
				 */
				if (tags.length > MIN_TAGS_PER_PHOTO
						&& !p.getTitle().equals("")) {
					pst1.setLong(1, p.getId());
					pst1.setString(2, truncate(p.getTitle(), 100));
					pst1.setURL(3, p.getUrl());

					// only save alias if < 20 characters
					String alias = p.getPathalias();
					if (alias != null) {
						if (alias.length() > 20)
							alias = null;
					}
					pst1.setString(4, alias);

					pst1.execute();

					// process tags

					for (String tag : tags) {
						pst2.setString(1, truncate(tag, 20));
						pst2.execute();

						pst3.setLong(1, p.getId());
						pst3.setString(2, truncate(tag, 20));
						pst3.execute();
					}

				}
				connection.commit();
			}

			connection.setAutoCommit(true);

		} catch (SQLException e) {
			System.out.println(e);
			Logger lgr = Logger.getLogger(DatabaseHelper.class.getName());
			lgr.log(Level.SEVERE, e.getMessage(), e);
		}
		System.out.println("Done data write");

		return true;

	}

	public boolean insertImageData(List<Photo> photos) {
		PreparedStatement pst1, pst2, pst3 = null;
		Iterator<Photo> iterator = photos.iterator();

		try {
			connection.setAutoCommit(false);

			pst1 = connection
					.prepareStatement("INSERT INTO colors(fk_idimage,quadrant,hue,sat,bri) VALUES (?,?,?,?,?)");
			pst2 = connection
					.prepareStatement("INSERT INTO histograms(fk_idimage,one,two,three,four,five,six,seven,eight) VALUES (?,?,?,?,?,?,?,?,?)");
			pst3 = connection
					.prepareStatement("UPDATE images SET imagedata=true WHERE idimage=?");

			while (iterator.hasNext()) {
				Photo p = (Photo) iterator.next();
				
				if(!p.isDud()){
					float[] c;
	
					// AVERAGE COLORS
					ColorData cd = p.getColorData();
	
					pst1.setLong(1, p.getId());
	
					// whole image average
					pst1.setInt(2, 0);
					c = cd.getAverageColor();
					setDbColorData(pst1, c[0], c[1], c[2]);
					pst1.execute();
	
					// quadrant averages
					for (int i = 0; i < cd.getAverageColors().length; i++) {
						c = cd.getAverageColors()[i];
						pst1.setInt(2, i + 1); // i.e. quadrants 1 to 9 inc
						setDbColorData(pst1, c[0], c[1], c[2]);
						pst1.execute();
					}
	
					// HISTOGRAM
					pst2.setLong(1, p.getId());
					int[] hist = cd.getHistogram();
					for (int i = 0; i < hist.length; i++) {
						pst2.setInt(i + 2, hist[i]);
					}
					pst2.execute();
	
					// Update has image data marker in images table
					pst3.setLong(1, p.getId());
					pst3.execute();
	
					connection.commit();
				} else {
					System.out.println("Dud! " + p.getId());
					removePhoto(p.getId(),false); //Colour data not added at this stage
				}
			}

			connection.setAutoCommit(true);

		} catch (SQLException e) {
			System.out.println(e);
			Logger lgr = Logger.getLogger(DatabaseHelper.class.getName());
			lgr.log(Level.SEVERE, e.getMessage(), e);
		}
		System.out.println("Done data write");

		return true;
	}

	public void removePhoto(long id,boolean incColourData) {
		try {
			Statement stmt = connection.createStatement();
			//Update count
			stmt.addBatch("UPDATE tags SET `count` = `count` - 1 WHERE tag IN (SELECT fk_tag FROM image_tag_link WHERE fk_idimage = "+id+")");
			//Remove in tag link table
			stmt.addBatch("DELETE FROM image_tag_link WHERE fk_idimage = "+id);
			stmt.addBatch("DELETE FROM images WHERE idimage = "+id);
			//Remove unused tags from tag table
			stmt.addBatch("DELETE FROM tags WHERE `count` = 0");
			if (incColourData){
				stmt.addBatch("DELETE FROM colors WHERE fk_idimage = "+id);
				stmt.addBatch("DELETE FROM histograms WHERE fk_idimage = "+id);
			}
			int[] updateCounts = stmt.executeBatch();
			if (!connection.getAutoCommit()) connection.commit();
		} catch (BatchUpdateException  e) {
			System.out.println(e);
			Logger lgr = Logger.getLogger(DatabaseHelper.class.getName());
			lgr.log(Level.SEVERE, e.getMessage(), e);
		}catch (SQLException  e) {
			System.out.println(e);
			Logger lgr = Logger.getLogger(DatabaseHelper.class.getName());
			lgr.log(Level.SEVERE, e.getMessage(), e);
		}	
	}

	private static void setDbColorData(PreparedStatement ps, float hue,
			float sat, float bri) throws SQLException {
		ps.setFloat(3, hue);
		ps.setFloat(4, sat);
		ps.setFloat(5, bri);
	}

	public void close() {

		if (connection != null)
			try {
				connection.close();
			} catch (SQLException ignore) {
			}

	}

	private static Connection getConnection(String dbURL, String user,
			String password) throws SQLException, ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");
		return DriverManager.getConnection(dbURL, user, password);
	}

	public static String truncate(String s, int length) {
		if (s != null && s.length() > length)
			return s.substring(0, length);
		return s;
	}
	
	public int getProcessedImageCount(){
		int count=0;
		try {
			Statement statement = connection.createStatement();

			// Result set get the result of the SQL query
			String query = "SELECT COUNT(*) FROM images WHERE imagedata=true";
			ResultSet resultSet = statement.executeQuery(query);

			while (resultSet.next()) { // go to next row
				count = resultSet.getInt("COUNT(*)");
			}
		} catch (SQLException e) {
			System.out.println(e);
			Logger lgr = Logger.getLogger(DatabaseHelper.class.getName());
			lgr.log(Level.SEVERE, e.getMessage(), e);
		}
		return count;
	}
	
	public int getTotalImageCount(){
		int count=0;
		try {
			Statement statement = connection.createStatement();

			// Result set get the result of the SQL query
			String query = "SELECT COUNT(*) FROM images";
			ResultSet resultSet = statement.executeQuery(query);

			

			while (resultSet.next()) { // go to next row
				count = resultSet.getInt("COUNT(*)");
			}
		} catch (SQLException e) {
			System.out.println(e);
			Logger lgr = Logger.getLogger(DatabaseHelper.class.getName());
			lgr.log(Level.SEVERE, e.getMessage(), e);
		}
		return count;
	}

	public List<Photo> getUnProcessedPhotos(int imagesAtATime) {
		List<Photo> photos = new ArrayList<Photo>();
		try {
			Statement statement = connection.createStatement();

			// Result set get the result of the SQL query
			String query = "SELECT * FROM images WHERE imagedata=false LIMIT "
					+ imagesAtATime;
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) { // go to next row
				// only need the image id and the url
				Photo p = new Photo();
				p.setId(resultSet.getLong("idimage"));
				p.setUrl(resultSet.getURL("url"));
				photos.add(p);
			}
		} catch (SQLException e) {
			System.out.println(e);
			Logger lgr = Logger.getLogger(DatabaseHelper.class.getName());
			lgr.log(Level.SEVERE, e.getMessage(), e);
		}
		return photos;
	}
	
	public Photo getPhotoById(long id){
		Photo p = new Photo();
		try {
			Statement statement = connection.createStatement();

			// Result set get the result of the SQL query
			String query = "SELECT * FROM images WHERE idimage= "
					+ id;
			ResultSet resultSet = statement.executeQuery(query);
			PreparedStatement pst1, pst2, pst3 = null;

			while (resultSet.next()) { // go to next row
				// only need the image id and the url
				p.setId(resultSet.getLong("idimage"));
				p.setUrl(resultSet.getURL("url"));
			}
		} catch (SQLException e) {
			System.out.println(e);
			Logger lgr = Logger.getLogger(DatabaseHelper.class.getName());
			lgr.log(Level.SEVERE, e.getMessage(), e);
		}
		
		return p;
		
	}
	


}
